Oracle will not perform a Hash Cluster scan

If you are here, you have already:

• Run your SQL through Explain Plan and confirmed that the desired cluster scan is not being used.
• Checked the statistics on the underlying table.
• Confirmed that the table is clustered.

Oracle will refuse to perform a hash cluster scan for a number of reasons, but they boil down to two possibilities:

• The structure of the SQL is such that Oracle cannot use a hash cluster scan
• Oracle's cost based optimizer is electing not to use the hash cluster scan

To find out which of these is true for your SQL, add a HASH hint to your SQL.
eg.

SELECT /*+ HASH(a) */ a.*
FROM emp a
WHERE a.emp_no = 111

Now run the SQL through Explain Plan. Is the cluster being used now? If the cluster is now being scanned, it means that the Cost Based Optimiser doesn't think the cluster is very useful for your SQL. Have you checked the statistics? Try running the SQL again with the HASH hint; is the performance better now? If the performance is still poor, then Oracle was probably right to ignore the cluster - you almost certainly have a Bad Cluster problem.

If the HASH hint did not work (ie. Oracle is still using Full Table Scan or an Index Scan), then there is something about the SQL preventing the hash cluster scan from being used.

• How is the cluster key used in the WHERE clause?
• Is any part of it altered by functions, arithmetic, concatenation?
• Is any part of it used with NOT or != operators?
• Is the cluster key used within OR or NOT predicates?
• If the cluster key contains multiple columns (concatenated key), are you using just the leading columns?
• Do you have a two table join? What order are the tables joined in?
• Are you casting the cluster key from a string to a number?
• Is the indexed column used with a sub-query clause?
  eg.
  • WHERE col IN (SELECT ...)
  • WHERE col = (SELECT ...)
  • WHERE col >= ANY (SELECT ...)
  • WHERE col <= ALL (SELECT ...)
  • WHERE col NOT IN (SELECT ...)

One of the bullet points above should determine why the hash cluster scan is not being used. If not, then work on the process of elimination. First confirm that oracle recognises the hash cluster - run the following through Explain Plan

SELECT /*+ HASH(a)*/ *
FROM table_name a
WHERE clust_col = :val

If this simple SQL refuses to perform a hash cluster scan, then there is a fundamental problem. Get the DBA to rebuild the cluster.

If the sample SQL above does use a hash cluster scan, then start with your original SQL and remove lines one at a time, running each successively smaller SQL through Explain Plan as you go. Somewhere between the original SQL and the one above, the hash cluster scan will start to work. Once you find the problem, check the bullet points above to see whether any of them explain the fault. If not, discuss the problem with the DBA.